import pymysql
from settings import dbParmas
class DbHelper():
    def __init__(self, params):
        '''连接参数字典'''
        self.conn = pymysql.Connect(**dbParmas)
        self.cursor = self.conn.cursor(pymysql.cursors.DictCursor)
        self.params = {
            'field': '*',
            'tables': '',
            'where': '',
            'group by': '',
            'having': '',
            'order by': '',
            'limit': ''
        }
    def __del__(self):
        self.cursor.close()
        self.conn.close()
    def init_parm(self):
        self.params = {
            'field': '*',
            'tables': '',
            'where': '',
            'group by': '',
            'having': '',
            'order by': '',
            'limit': ''
        }
    def where(self, **kwargs):
        #运算符字典
        ops = {
            'ne': '<>',
            'gt': '>',
            'ge': '>=',
            'lt': '<',
            'le': '<=',
            'contains': 'like',
            'in' : 'in',
            'nin':'not in'
        }
        result = " where "
        for key in kwargs:
            keys = key.split('__')
            if len(keys) > 1:
                op = ops[keys[1]]
                if isinstance(kwargs[key], str):
                    result += keys[0] + op + "'" + kwargs[key] + "' and "
                else:
                    result += keys[0] + op + kwargs[key] + ' and '
            else:#运算符为=
                if isinstance(kwargs[key], str):
                    result += keys[0] + "='" + kwargs[key] + "' and "
                else:
                    result += keys[0] + '=' + kwargs[key] + ' and '
        result = result.strip(' and ')
        self.params['where'] = result
        return self
    def table(self, tables):
        self.params['tables'] = tables
        return self
    def field(self, fields):
        self.params['field'] = fields
        return self
    def group_by(self, params):

        self.params['group by'] = "group by {}".format(params)
        return self
    def select(self):
        sql = "SELECT {field} from {tables} {where} {group by} {having} {order by} {limit}"
        sql = sql.format(**self.params)
        return self.query(sql)
    def query(self, sql):
        self.init_parm()
        self.cursor.execute(sql)
        return self.cursor.fetchall()
    def order_by(self, params):
        self.params['order by'] = "order by {}".format(params)
        return self
    def limit(self, params):
        self.params['limit'] = "limit {}".format(params)
        return self
    def having(self, params):
        self.params['having'] = "having {}".format(params)
        return self
    def update(self,**kwargs):
        kwargs = {key: "'" + pymysql.escape_string(value) + "'" if isinstance(value,str) else value for key,value in kwargs.items()}
        #字典变成字符串
        res = ','.join([key + "=" + str(value) for key,value in kwargs.items()])
        self.params['keyvalues'] = res
        sql = "update {tables} set {keyvalues} {where}".format(**self.params)
        return self.excute(sql)

    def excute(self,sql):
        self.sql = sql
        self.init_parm()
        try:
            self.cursor.execute(sql)
            self.conn.commit()
            return True
        except Exception as e:
            print(e)
            self.conn.rollback()
            return False

if __name__ == '__main__':
    db = DbHelper(dbParmas)
    print(db.conn)
    print(db.where(sno='108').table('student').update(ssex='女'))




